from cmath import isnan

import akshare as ak
import configparser
import logging
import multiprocessing
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from joblib import Parallel, delayed
from tqdm import tqdm
from stock_a.common.utils import time_util
from stock_a.common.db import stock_info

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')


class StockInfoFetcher:

    def __init__(self):
        self.quant_config = configparser.ConfigParser()
        self.quant_config.read('../config/quant_config.ini')
        self.GROUP_DATE_INDEX = 23

    def build_or_update_code_name(self):
        """
        构建id -> 股票代码的映射关系，并维护在数据库表中
        连续的id属性在后续特征构建中可以避免稀疏问题
        """
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `id`, `code`, `name` FROM code_to_name')
        data = cursor.fetchall()
        exist_code_set = set()
        next_id = -1
        for row in data:
            exist_code_set.add(row[1])
            next_id = max(next_id, row[0])
        stock_info_a_code_name_df = ak.stock_info_a_code_name()
        new_stock_info = list()
        for row in stock_info_a_code_name_df.itertuples():
            # 前缀  北交所：8；创业板：3；科创板：688；沪市：60；深市：00
            # 只取沪市和深市区
            code = getattr(row, 'code')
            if not code.startswith('00') and not code.startswith('60'):
                continue
            name = getattr(row, 'name')
            if code not in exist_code_set:
                next_id += 1
                new_stock_info.append((next_id, code, name))
        if len(new_stock_info) > 0:
            sql = 'insert into code_to_name (`id`, `code`, `name`) values (%s, %s, %s)'
            cursor.executemany(sql, new_stock_info)
            db.commit()
            cursor.close()
        db.close()

    def build_or_update_hfq_factors(self):
        """
        构建后复权因子
        1. 使用比例法复权：比例法复权的回测效果从逻辑上来说是在除权日前一交易日尾盘以收盘价卖出所有股票，
           然后将所有资金在除权日开盘时以除权价（即交易所发布的昨收价PreClose）全部买回（不考虑交易费用），即不参加分红、配股等分配。
        2. 东财接口用的是加减法复权，该方法回测时涨跌幅是有偏的
        3. 新浪接口有请求数量限制，只用来拉取后复权因子，历史天级数据使用东财接口，用不复权股价自己计算
        参考：https://zhuanlan.zhihu.com/p/671295006
             https://xueqiu.com/5159163033/129561385
        """
        code_set = stock_info.get_all_stock_code_set()
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `code`, max(`date`) FROM code_to_hfq_factor GROUP BY `code`')
        data = cursor.fetchall()
        code_max_date_dict = dict()
        for row in data:
            code_max_date_dict[row[0]] = row[1]
        cursor.close()
        db.close()
        Parallel(n_jobs=multiprocessing.cpu_count())(delayed(StockInfoFetcher.__execute_update_hfq_factors_per_stock)(
            self, code, code_max_date_dict) for code in tqdm(code_set, '构建/更新后复权因子'))

    def __execute_update_hfq_factors_per_stock(self, code, code_max_date_dict):
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        prefix = 'sz' if code.startswith('00') else 'sh'
        stock_zh_a_daily = ak.stock_zh_a_daily(prefix + code, adjust='hfq-factor')
        if len(stock_zh_a_daily) > 0:
            max_date = code_max_date_dict[code] if code in code_max_date_dict else None
            data = list()
            for row in stock_zh_a_daily.itertuples():
                date = getattr(row, 'date')
                hfq_factor = getattr(row, 'hfq_factor')
                if max_date is None or date > max_date:
                    data.append((code, max(date, datetime(1980, 1, 1)), hfq_factor))
            sql = 'INSERT INTO code_to_hfq_factor (`code`, `date`, `hfq_factor`) VALUES (%s, %s, %s)'
            if len(data) > 0:
                cursor.executemany(sql, data)
                db.commit()
        else:
            raise IOError('拉取后复权接口需要确认是否异常')
        cursor.close()
        db.close()

    def build_or_update_day_k(self, biz_date):
        """
        构建/更新股票交易日线流水表
          - 用code hash取模做分表条件
          - 保留不复权参数
          - 复权仅保留后复权，同时包含比例后复权和加减后复权两种方式
          - 比例后复权东财接口不支持，通过比例后复权因子*不复权参数 计算出来
        """
        code_set = stock_info.get_all_stock_code_set()
        Parallel(n_jobs=multiprocessing.cpu_count())(delayed(StockInfoFetcher.__execute_update_day_k_per_stock)(
            self, code, biz_date) for code in tqdm(code_set, '更新股票交易日线流水表'))

    def __execute_update_day_k_per_stock(self, code, biz_date):
        earliest_date = self.quant_config['stock_info']['earliest_date']
        table_name = stock_info.get_stock_hist_day_k_table_name(code)
        hfq_factor_array = self.__get_hfq_factors_by_code(code)
        if len(hfq_factor_array) == 0:
            raise Exception('get hfq_factor failed')
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `date`, `close_hfq_by_factor` FROM {} WHERE `code` = %s ORDER BY date DESC LIMIT 1'.
                       format(table_name), code)
        data = cursor.fetchone()
        start_date = (data[0] + timedelta(days=1)).strftime('%Y%m%d') if data is not None else earliest_date
        last_close_hfq_by_factor = data[1] if data is not None else None
        end_date = biz_date.strftime('%Y%m%d')
        stock_hist_df = ak.stock_zh_a_hist(symbol=code, period="daily",
                                           start_date=start_date, end_date=end_date, adjust="")
        stock_hist_hfq_df = ak.stock_zh_a_hist(symbol=code, period="daily",
                                               start_date=start_date, end_date=end_date, adjust="hfq")
        assert len(stock_hist_df) == len(stock_hist_hfq_df)
        if len(stock_hist_df) == 0:
            return
        stock_hist_df.sort_values(by=['日期'], inplace=True)
        stock_hist_hfq_df.sort_values(by=['日期'], inplace=True)
        hfq_factor_index = 0
        stock_info_list = list()
        for i in range(len(stock_hist_df)):
            stock = stock_hist_df.loc[i]
            stock_hfq = stock_hist_hfq_df.loc[i]
            date = stock['日期']
            assert date == stock_hfq['日期']
            if hfq_factor_index >= len(hfq_factor_array) - 1 or date < hfq_factor_array[hfq_factor_index + 1][0].date():
                hfq_factor = hfq_factor_array[hfq_factor_index][1]
            else:
                while hfq_factor_index < len(hfq_factor_array) - 1:
                    hfq_factor_index += 1
                    if (date >= hfq_factor_array[hfq_factor_index][0].date() and
                            (hfq_factor_index == len(hfq_factor_array) - 1 or
                             date < hfq_factor_array[hfq_factor_index + 1][0].date())):
                        break
                hfq_factor = hfq_factor_array[hfq_factor_index][1]
            open = stock['开盘']
            open_hfq = stock_hfq['开盘']
            open_hfq_by_factor = stock['开盘'] * hfq_factor
            close = stock['收盘']
            close_hfq = stock_hfq['收盘']
            close_hfq_by_factor = stock['收盘'] * hfq_factor
            high = stock['最高']
            high_hfq = stock_hfq['最高']
            high_hfq_by_factor = stock['最高'] * hfq_factor
            low = stock['最低']
            low_hfq = stock_hfq['最低']
            low_hfq_by_factor = stock['最低'] * hfq_factor
            amplitude = stock['振幅']
            amplitude_hfq = stock_hfq['振幅']
            amplitude_hfq_by_factor = stock['振幅']
            fluctuate = stock['涨跌幅']
            fluctuate_hfq = stock_hfq['涨跌幅']
            # 计算涨跌幅要用当日收盘价跟昨日收盘价比
            if last_close_hfq_by_factor is None:
                stock_ipo_summary_cninfo_df = ak.stock_ipo_summary_cninfo(symbol=code)
                last_close_hfq_by_factor = stock_ipo_summary_cninfo_df.loc[0]['发行价格']
                # IPO后第一个交易日如果获取不到发行价，则以当日开盘价作为发行价
                if isnan(last_close_hfq_by_factor):
                    last_close_hfq_by_factor = open_hfq_by_factor
            fluctuate_hfq_by_factor = (close_hfq_by_factor - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
            last_close_hfq_by_factor = close_hfq_by_factor
            volume = stock['成交量']
            amount = stock['成交额']
            turnover = stock['换手率']
            stock_data = [code, date, open, open_hfq, open_hfq_by_factor, close, close_hfq, close_hfq_by_factor,
                          high, high_hfq, high_hfq_by_factor, low, low_hfq, low_hfq_by_factor,
                          amplitude, amplitude_hfq, amplitude_hfq_by_factor,
                          fluctuate, fluctuate_hfq, fluctuate_hfq_by_factor, volume, amount, turnover]
            stock_info_list.append(stock_data)
        if len(stock_info_list) > 0:
            sql = (('INSERT INTO {} (`code`, `date`, `open`, `open_hfq`, `open_hfq_by_factor`, '
                    '`close`, `close_hfq`, `close_hfq_by_factor`, `high`, `high_hfq`, `high_hfq_by_factor`, '
                    '`low`, `low_hfq`, `low_hfq_by_factor`, `amplitude`, `amplitude_hfq`, `amplitude_hfq_by_factor`, '
                    '`fluctuate`, `fluctuate_hfq`, `fluctuate_hfq_by_factor`, `volume`, `amount`, `turnover`) '
                    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,'
                    ' %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    ).format(
                table_name))
            cursor.executemany(sql, stock_info_list)
            db.commit()
        cursor.close()
        db.close()

    def build_or_update_week_k(self, biz_date):
        """
        构建/更新股票交易周线流水表
          - 起始时间为每周一，取周一到周五区间数据
          - 用code hash取模做分表条件
          - 保留不复权参数
          - 复权仅保留后复权，同时包含比例后复权和加减后复权两种方式
          - 依赖日线表的结果计算而来
        """
        code_set = stock_info.get_all_stock_code_set()
        # code_set = ['600900']
        Parallel(n_jobs=multiprocessing.cpu_count())(delayed(StockInfoFetcher.__execute_update_week_k_per_stock)(
            self, code, biz_date) for code in tqdm(code_set, '更新股票交易周线流水表'))

    def __execute_update_week_k_per_stock(self, code, biz_date):
        earliest_date = self.quant_config['stock_info']['earliest_date']
        week_table_name = stock_info.get_stock_hist_week_k_table_name(code)
        day_table_name = stock_info.get_stock_hist_day_k_table_name(code)
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `date`, `close`, `close_hfq`, `close_hfq_by_factor` FROM {} '
                       'WHERE `code` = %s ORDER BY `date` DESC LIMIT 1'.
                       format(week_table_name), code)
        data = cursor.fetchone()
        start_date = (data[0] + timedelta(days=7)).strftime('%Y%m%d') if data is not None else earliest_date
        last_close = data[1] if data is not None else None
        last_close_hfq = data[2] if data is not None else None
        last_close_hfq_by_factor = data[3] if data is not None else None

        if last_close is None:
            # 发行首周，前日收盘价用首日收盘价/(1+涨跌幅)
            cursor.execute('SELECT `close`, `fluctuate` FROM {} WHERE `code` = %s ORDER BY date LIMIT 1'.
                           format(day_table_name), code)
            first_data = cursor.fetchone()
            if first_data is None:
                # 还没有交易数据的股票，跳过
                return
            last_close = first_data[0] / (1.0 + first_data[1]/100.0)
            last_close_hfq = last_close
            last_close_hfq_by_factor = last_close_hfq
        end_date = time_util.nearest_last_friday(biz_date).strftime('%Y%m%d')
        day_stock_sql = 'SELECT * FROM {} WHERE `code`=%s AND (`date` between %s and %s)'.format(day_table_name)
        cursor.execute(day_stock_sql, (code, start_date, end_date))
        data = cursor.fetchall()
        if len(data) == 0:
            return
        day_stock_rows = list()
        for row in data:
            day_stock_row = list(row)
            day_stock_row.append(time_util.nearest_last_monday(row[stock_info.DATE_INDEX]))
            day_stock_rows.append(day_stock_row)
        week_stock_rows = list()
        each_week_stock_row = None
        each_week_date = None
        for index, day_stock_row in enumerate(day_stock_rows):
            if each_week_date != day_stock_row[self.GROUP_DATE_INDEX]:
                if each_week_stock_row is not None:
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    open_hfq = each_week_stock_row[stock_info.OPEN_HFQ_INDEX]
                    if open_hfq <= 0:
                        open_hfq = 0.01
                    each_week_stock_row[stock_info.AMPLITUDE_INDEX] = (each_week_stock_row[stock_info.HIGH_INDEX] - each_week_stock_row[stock_info.LOW_INDEX]) / each_week_stock_row[stock_info.OPEN_INDEX]*100.0
                    each_week_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_week_stock_row[stock_info.HIGH_HFQ_INDEX] - each_week_stock_row[stock_info.LOW_HFQ_INDEX])/open_hfq*100.0
                    each_week_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_week_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] - each_week_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])/each_week_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]*100.0
                    if last_close is None:
                        last_close = each_week_stock_row[stock_info.OPEN_INDEX]
                    each_week_stock_row[stock_info.FLUCTUATE_INDEX] = (each_week_stock_row[stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                    last_close = each_week_stock_row[stock_info.CLOSE_INDEX]
                    if last_close_hfq is None:
                        last_close_hfq = each_week_stock_row[stock_info.OPEN_HFQ_INDEX]
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    if last_close_hfq <= 0:
                        last_close_hfq = 0.01
                    each_week_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_week_stock_row[stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                    last_close_hfq = each_week_stock_row[stock_info.CLOSE_HFQ_INDEX]
                    if last_close_hfq_by_factor is None:
                        last_close_hfq_by_factor = each_week_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                    each_week_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_week_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                    last_close_hfq_by_factor = each_week_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                    week_stock_rows.append(each_week_stock_row)
                each_week_date = day_stock_row[self.GROUP_DATE_INDEX]
                each_week_stock_row = list()
                each_week_stock_row.append(day_stock_row[stock_info.CODE_INDEX])
                each_week_stock_row.append(day_stock_row[self.GROUP_DATE_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.OPEN_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.OPEN_HFQ_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.CLOSE_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.CLOSE_HFQ_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.HIGH_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.LOW_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.LOW_HFQ_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.AMPLITUDE_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.AMPLITUDE_HFQ_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.FLUCTUATE_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.FLUCTUATE_HFQ_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.VOLUME_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.AMOUNT_INDEX])
                each_week_stock_row.append(day_stock_row[stock_info.TURNOVER_INDEX])
            else:
                each_week_stock_row[stock_info.CLOSE_INDEX] = day_stock_row[stock_info.CLOSE_INDEX]
                each_week_stock_row[stock_info.CLOSE_HFQ_INDEX] = day_stock_row[stock_info.CLOSE_HFQ_INDEX]
                each_week_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] = day_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                each_week_stock_row[stock_info.HIGH_INDEX] = max(day_stock_row[stock_info.HIGH_INDEX], each_week_stock_row[stock_info.HIGH_INDEX])
                each_week_stock_row[stock_info.HIGH_HFQ_INDEX] = max(day_stock_row[stock_info.HIGH_HFQ_INDEX], each_week_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_week_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] = max(day_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX], each_week_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row[stock_info.LOW_INDEX] = min(day_stock_row[stock_info.LOW_INDEX], each_week_stock_row[stock_info.LOW_INDEX])
                each_week_stock_row[stock_info.LOW_HFQ_INDEX] = min(day_stock_row[stock_info.LOW_HFQ_INDEX], each_week_stock_row[stock_info.LOW_HFQ_INDEX])
                each_week_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX] = min(day_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX], each_week_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_week_stock_row[stock_info.VOLUME_INDEX] += day_stock_row[stock_info.VOLUME_INDEX]
                each_week_stock_row[stock_info.AMOUNT_INDEX] += day_stock_row[stock_info.AMOUNT_INDEX]
                each_week_stock_row[stock_info.TURNOVER_INDEX] += day_stock_row[stock_info.TURNOVER_INDEX]
            if index == len(day_stock_rows) - 1:
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                open_hfq = each_week_stock_row[stock_info.OPEN_HFQ_INDEX]
                if open_hfq <= 0:
                    open_hfq = 0.01
                each_week_stock_row[stock_info.AMPLITUDE_INDEX] = (each_week_stock_row[stock_info.HIGH_INDEX] - each_week_stock_row[stock_info.LOW_INDEX]) / each_week_stock_row[stock_info.OPEN_INDEX]*100.0
                each_week_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_week_stock_row[stock_info.HIGH_HFQ_INDEX] - each_week_stock_row[stock_info.LOW_HFQ_INDEX])/open_hfq*100.0
                each_week_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_week_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] - each_week_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])/each_week_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]*100.0
                if last_close is None:
                    last_close = each_week_stock_row[stock_info.OPEN_INDEX]
                each_week_stock_row[stock_info.FLUCTUATE_INDEX] = (each_week_stock_row[stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                last_close = each_week_stock_row[stock_info.CLOSE_INDEX]
                if last_close_hfq is None:
                    last_close_hfq = each_week_stock_row[stock_info.OPEN_HFQ_INDEX]
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                if last_close_hfq <= 0:
                    last_close_hfq = 0.01
                each_week_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_week_stock_row[stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                last_close_hfq = each_week_stock_row[stock_info.CLOSE_HFQ_INDEX]
                if last_close_hfq_by_factor is None:
                    last_close_hfq_by_factor = each_week_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                each_week_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_week_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                last_close_hfq_by_factor = each_week_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                week_stock_rows.append(each_week_stock_row)
                each_week_stock_row = None
        if len(week_stock_rows) > 0:
            sql = (('INSERT INTO {} (`code`, `date`, `open`, `open_hfq`, `open_hfq_by_factor`, '
                    '`close`, `close_hfq`, `close_hfq_by_factor`, `high`, `high_hfq`, `high_hfq_by_factor`, '
                    '`low`, `low_hfq`, `low_hfq_by_factor`, `amplitude`, `amplitude_hfq`, `amplitude_hfq_by_factor`, '
                    '`fluctuate`, `fluctuate_hfq`, `fluctuate_hfq_by_factor`, `volume`, `amount`, `turnover`) '
                    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,'
                    ' %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    ).format(
                week_table_name))
            cursor.executemany(sql, week_stock_rows)
            db.commit()
        cursor.close()
        db.close()

    def build_or_update_month_k(self, biz_date):
        code_set = stock_info.get_all_stock_code_set()
        # code_set = ['600900']
        Parallel(n_jobs=multiprocessing.cpu_count())(delayed(StockInfoFetcher.__execute_update_month_k_per_stock)(
            self, code, biz_date) for code in tqdm(code_set, '更新股票交易月线流水表'))

    def __execute_update_month_k_per_stock(self, code, biz_date):
        earliest_date = self.quant_config['stock_info']['earliest_date']
        month_table_name = 'stock_hist_month_k'
        day_table_name = stock_info.get_stock_hist_day_k_table_name(code)
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `date`, `close`, `close_hfq`, `close_hfq_by_factor` FROM {} '
                       'WHERE `code` = %s ORDER BY `date` DESC LIMIT 1'.
                       format(month_table_name), code)
        data = cursor.fetchone()
        start_date = time_util.nearest_next_first_day_of_month(data[0] + timedelta(days=1)).strftime('%Y%m%d') if data is not None else earliest_date
        last_close = data[1] if data is not None else None
        last_close_hfq = data[2] if data is not None else None
        last_close_hfq_by_factor = data[3] if data is not None else None
        if last_close is None:
            # 发行首周，前日收盘价用首日收盘价/(1+涨跌幅)
            cursor.execute('SELECT `close`, `fluctuate` FROM {} WHERE `code` = %s ORDER BY date LIMIT 1'.
                           format(day_table_name), code)
            first_data = cursor.fetchone()
            if first_data is None:
                # 还没有交易数据的股票，跳过
                return
            last_close = first_data[0] / (1.0 + first_data[1]/100.0)
            last_close_hfq = last_close
            last_close_hfq_by_factor = last_close_hfq
        end_date = time_util.nearest_last_last_day_of_month(biz_date).strftime('%Y%m%d')
        day_stock_sql = 'SELECT * FROM {} WHERE `code`=%s AND (`date` between %s and %s)'.format(day_table_name)
        cursor.execute(day_stock_sql, (code, start_date, end_date))
        data = cursor.fetchall()
        if len(data) == 0:
            return
        day_stock_rows = list()
        for row in data:
            day_stock_row = list(row)
            day_stock_row.append(time_util.nearest_last_first_day_of_month(row[stock_info.DATE_INDEX]))
            day_stock_rows.append(day_stock_row)
        month_stock_rows = list()
        each_month_stock_row = None
        each_month_date = None
        for index, day_stock_row in enumerate(day_stock_rows):
            if each_month_date != day_stock_row[self.GROUP_DATE_INDEX]:
                if each_month_stock_row is not None:
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    open_hfq = each_month_stock_row[stock_info.OPEN_HFQ_INDEX]
                    if open_hfq <= 0:
                        open_hfq = 0.01
                    each_month_stock_row[stock_info.AMPLITUDE_INDEX] = (each_month_stock_row[stock_info.HIGH_INDEX] -
                                                                 each_month_stock_row[stock_info.LOW_INDEX]) / \
                                                                each_month_stock_row[stock_info.OPEN_INDEX] * 100.0
                    each_month_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_month_stock_row[stock_info.HIGH_HFQ_INDEX] -
                                                                     each_month_stock_row[
                                                                         stock_info.LOW_HFQ_INDEX]) / open_hfq * 100.0
                    each_month_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_month_stock_row[
                                                                                   stock_info.HIGH_HFQ_BY_FACTOR_INDEX] -
                                                                               each_month_stock_row[
                                                                                   stock_info.LOW_HFQ_BY_FACTOR_INDEX]) / \
                                                                              each_month_stock_row[
                                                                                  stock_info.OPEN_HFQ_BY_FACTOR_INDEX] * 100.0
                    if last_close is None:
                        last_close = each_month_stock_row[stock_info.OPEN_INDEX]
                    each_month_stock_row[stock_info.FLUCTUATE_INDEX] = (each_month_stock_row[
                                                                     stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                    last_close = each_month_stock_row[stock_info.CLOSE_INDEX]
                    if last_close_hfq is None:
                        last_close_hfq = each_month_stock_row[stock_info.OPEN_HFQ_INDEX]
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    if last_close_hfq <= 0:
                        last_close_hfq = 0.01
                    each_month_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_month_stock_row[
                                                                         stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                    last_close_hfq = each_month_stock_row[stock_info.CLOSE_HFQ_INDEX]
                    if last_close_hfq_by_factor is None:
                        last_close_hfq_by_factor = each_month_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                    each_month_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_month_stock_row[
                                                                                   stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                    last_close_hfq_by_factor = each_month_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                    month_stock_rows.append(each_month_stock_row)
                each_month_date = day_stock_row[stock_info.GROUP_DATE_INDEX]
                each_month_stock_row = list()
                each_month_stock_row.append(day_stock_row[stock_info.CODE_INDEX])
                each_month_stock_row.append(day_stock_row[self.GROUP_DATE_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.OPEN_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.OPEN_HFQ_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.CLOSE_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.CLOSE_HFQ_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.HIGH_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.LOW_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.LOW_HFQ_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.AMPLITUDE_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.AMPLITUDE_HFQ_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.FLUCTUATE_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.FLUCTUATE_HFQ_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.VOLUME_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.AMOUNT_INDEX])
                each_month_stock_row.append(day_stock_row[stock_info.TURNOVER_INDEX])
            else:
                each_month_stock_row[stock_info.CLOSE_INDEX] = day_stock_row[stock_info.CLOSE_INDEX]
                each_month_stock_row[stock_info.CLOSE_HFQ_INDEX] = day_stock_row[stock_info.CLOSE_HFQ_INDEX]
                each_month_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] = day_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                each_month_stock_row[stock_info.HIGH_INDEX] = max(day_stock_row[stock_info.HIGH_INDEX],
                                                           each_month_stock_row[stock_info.HIGH_INDEX])
                each_month_stock_row[stock_info.HIGH_HFQ_INDEX] = max(day_stock_row[stock_info.HIGH_HFQ_INDEX],
                                                               each_month_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_month_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] = max(day_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX],
                                                                         each_month_stock_row[
                                                                             stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row[stock_info.LOW_INDEX] = min(day_stock_row[stock_info.LOW_INDEX],
                                                          each_month_stock_row[stock_info.LOW_INDEX])
                each_month_stock_row[stock_info.LOW_HFQ_INDEX] = min(day_stock_row[stock_info.LOW_HFQ_INDEX],
                                                              each_month_stock_row[stock_info.LOW_HFQ_INDEX])
                each_month_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX] = min(day_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX],
                                                                        each_month_stock_row[
                                                                            stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_month_stock_row[stock_info.VOLUME_INDEX] += day_stock_row[stock_info.VOLUME_INDEX]
                each_month_stock_row[stock_info.AMOUNT_INDEX] += day_stock_row[stock_info.AMOUNT_INDEX]
                each_month_stock_row[stock_info.TURNOVER_INDEX] += day_stock_row[stock_info.TURNOVER_INDEX]
            if index == len(day_stock_rows) - 1:
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                open_hfq = each_month_stock_row[stock_info.OPEN_HFQ_INDEX]
                if open_hfq <= 0:
                    open_hfq = 0.01
                each_month_stock_row[stock_info.AMPLITUDE_INDEX] = (each_month_stock_row[stock_info.HIGH_INDEX] - each_month_stock_row[
                    stock_info.LOW_INDEX]) / each_month_stock_row[stock_info.OPEN_INDEX] * 100.0
                each_month_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_month_stock_row[stock_info.HIGH_HFQ_INDEX] -
                                                                 each_month_stock_row[
                                                                     stock_info.LOW_HFQ_INDEX]) / open_hfq * 100.0
                each_month_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_month_stock_row[
                                                                               stock_info.HIGH_HFQ_BY_FACTOR_INDEX] -
                                                                           each_month_stock_row[
                                                                               stock_info.LOW_HFQ_BY_FACTOR_INDEX]) / \
                                                                          each_month_stock_row[
                                                                              stock_info.OPEN_HFQ_BY_FACTOR_INDEX] * 100.0
                if last_close is None:
                    last_close = each_month_stock_row[stock_info.OPEN_INDEX]
                each_month_stock_row[stock_info.FLUCTUATE_INDEX] = (each_month_stock_row[
                                                                 stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                last_close = each_month_stock_row[stock_info.CLOSE_INDEX]
                if last_close_hfq is None:
                    last_close_hfq = each_month_stock_row[stock_info.OPEN_HFQ_INDEX]
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                if last_close_hfq <= 0:
                    last_close_hfq = 0.01
                each_month_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_month_stock_row[
                                                                     stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                last_close_hfq = each_month_stock_row[stock_info.CLOSE_HFQ_INDEX]
                if last_close_hfq_by_factor is None:
                    last_close_hfq_by_factor = each_month_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                each_month_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_month_stock_row[
                                                                               stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                last_close_hfq_by_factor = each_month_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                month_stock_rows.append(each_month_stock_row)
                each_month_stock_row = None
        if len(month_stock_rows) > 0:
            sql = (('INSERT INTO {} (`code`, `date`, `open`, `open_hfq`, `open_hfq_by_factor`, '
                    '`close`, `close_hfq`, `close_hfq_by_factor`, `high`, `high_hfq`, `high_hfq_by_factor`, '
                    '`low`, `low_hfq`, `low_hfq_by_factor`, `amplitude`, `amplitude_hfq`, `amplitude_hfq_by_factor`, '
                    '`fluctuate`, `fluctuate_hfq`, `fluctuate_hfq_by_factor`, `volume`, `amount`, `turnover`) '
                    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,'
                    ' %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    ).format(
                month_table_name))
            cursor.executemany(sql, month_stock_rows)
            db.commit()
        cursor.close()
        db.close()

    def build_or_update_quarter_k(self, biz_date):
        code_set = stock_info.get_all_stock_code_set()
        # code_set = ['600900']
        Parallel(n_jobs=multiprocessing.cpu_count())(delayed(StockInfoFetcher.__execute_update_quarter_k_per_stock)(
            self, code, biz_date) for code in tqdm(code_set, '更新股票交易季线流水表'))

    def __execute_update_quarter_k_per_stock(self, code, biz_date):
        earliest_date = self.quant_config['stock_info']['earliest_date']
        quarter_table_name = 'stock_hist_quarter_k'
        month_table_name = 'stock_hist_month_k'
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `date`, `close`, `close_hfq`, `close_hfq_by_factor` FROM {} '
                       'WHERE `code` = %s ORDER BY `date` DESC LIMIT 1'.
                       format(quarter_table_name), code)
        data = cursor.fetchone()
        start_date = (data[0] + relativedelta(months=3)).strftime('%Y%m%d') if data is not None else earliest_date
        last_close = data[1] if data is not None else None
        last_close_hfq = data[2] if data is not None else None
        last_close_hfq_by_factor = data[3] if data is not None else None
        if last_close is None:
            # 发行首周，前日收盘价用首日收盘价/(1+涨跌幅)
            day_table_name = stock_info.get_stock_hist_day_k_table_name(code)
            cursor.execute('SELECT `close`, `fluctuate` FROM {} WHERE `code` = %s ORDER BY date LIMIT 1'.
                           format(day_table_name), code)
            first_data = cursor.fetchone()
            if first_data is None:
                # 还没有交易数据的股票，跳过
                return
            last_close = first_data[0] / (1.0 + first_data[1] / 100.0)
            last_close_hfq = last_close
            last_close_hfq_by_factor = last_close_hfq
        end_date = time_util.nearest_last_last_day_of_quarter(biz_date).strftime('%Y%m%d')
        month_stock_sql = 'SELECT * FROM {} WHERE `code`=%s AND (`date` between %s and %s)'.format(month_table_name)
        cursor.execute(month_stock_sql, (code, start_date, end_date))
        data = cursor.fetchall()
        if len(data) == 0:
            return
        month_stock_rows = list()
        for row in data:
            month_stock_row = list(row)
            month_stock_row.append(time_util.nearest_last_first_day_of_quarter(row[stock_info.DATE_INDEX]))
            month_stock_rows.append(month_stock_row)
        quarter_stock_rows = list()
        each_quarter_stock_row = None
        each_quarter_date = None
        for index, month_stock_row in enumerate(month_stock_rows):
            if each_quarter_date != month_stock_row[self.GROUP_DATE_INDEX]:
                if each_quarter_stock_row is not None:
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    open_hfq = each_quarter_stock_row[stock_info.OPEN_HFQ_INDEX]
                    if open_hfq <= 0:
                        open_hfq = 0.01
                    each_quarter_stock_row[stock_info.AMPLITUDE_INDEX] = (each_quarter_stock_row[stock_info.HIGH_INDEX] - each_quarter_stock_row[stock_info.LOW_INDEX]) / each_quarter_stock_row[stock_info.OPEN_INDEX] * 100.0
                    each_quarter_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_quarter_stock_row[stock_info.HIGH_HFQ_INDEX] - each_quarter_stock_row[stock_info.LOW_HFQ_INDEX]) / open_hfq * 100.0
                    each_quarter_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_quarter_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] - each_quarter_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX]) / each_quarter_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX] * 100.0
                    if last_close is None:
                        last_close = each_quarter_stock_row[stock_info.OPEN_INDEX]
                    each_quarter_stock_row[stock_info.FLUCTUATE_INDEX] = (each_quarter_stock_row[stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                    last_close = each_quarter_stock_row[stock_info.CLOSE_INDEX]
                    if last_close_hfq is None:
                        last_close_hfq = each_quarter_stock_row[stock_info.OPEN_HFQ_INDEX]
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    if last_close_hfq <= 0:
                        last_close_hfq = 0.01
                    each_quarter_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_quarter_stock_row[
                                                                         stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                    last_close_hfq = each_quarter_stock_row[stock_info.CLOSE_HFQ_INDEX]
                    if last_close_hfq_by_factor is None:
                        last_close_hfq_by_factor = each_quarter_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                    each_quarter_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_quarter_stock_row[
                                                                                   stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                    last_close_hfq_by_factor = each_quarter_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                    quarter_stock_rows.append(each_quarter_stock_row)
                each_quarter_date = month_stock_row[self.GROUP_DATE_INDEX]
                each_quarter_stock_row = list()
                each_quarter_stock_row.append(month_stock_row[stock_info.CODE_INDEX])
                each_quarter_stock_row.append(month_stock_row[self.GROUP_DATE_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.OPEN_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.OPEN_HFQ_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.CLOSE_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.CLOSE_HFQ_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.HIGH_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.LOW_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.LOW_HFQ_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.AMPLITUDE_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.AMPLITUDE_HFQ_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.FLUCTUATE_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.FLUCTUATE_HFQ_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.VOLUME_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.AMOUNT_INDEX])
                each_quarter_stock_row.append(month_stock_row[stock_info.TURNOVER_INDEX])
            else:
                each_quarter_stock_row[stock_info.CLOSE_INDEX] = month_stock_row[stock_info.CLOSE_INDEX]
                each_quarter_stock_row[stock_info.CLOSE_HFQ_INDEX] = month_stock_row[stock_info.CLOSE_HFQ_INDEX]
                each_quarter_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] = month_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                each_quarter_stock_row[stock_info.HIGH_INDEX] = max(month_stock_row[stock_info.HIGH_INDEX], each_quarter_stock_row[stock_info.HIGH_INDEX])
                each_quarter_stock_row[stock_info.HIGH_HFQ_INDEX] = max(month_stock_row[stock_info.HIGH_HFQ_INDEX], each_quarter_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_quarter_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] = max(month_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX], each_quarter_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row[stock_info.LOW_INDEX] = min(month_stock_row[stock_info.LOW_INDEX], each_quarter_stock_row[stock_info.LOW_INDEX])
                each_quarter_stock_row[stock_info.LOW_HFQ_INDEX] = min(month_stock_row[stock_info.LOW_HFQ_INDEX], each_quarter_stock_row[stock_info.LOW_HFQ_INDEX])
                each_quarter_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX] = min(month_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX], each_quarter_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_quarter_stock_row[stock_info.VOLUME_INDEX] += month_stock_row[stock_info.VOLUME_INDEX]
                each_quarter_stock_row[stock_info.AMOUNT_INDEX] += month_stock_row[stock_info.AMOUNT_INDEX]
                each_quarter_stock_row[stock_info.TURNOVER_INDEX] += month_stock_row[stock_info.TURNOVER_INDEX]
            if index == len(month_stock_rows) - 1:
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                open_hfq = each_quarter_stock_row[stock_info.OPEN_HFQ_INDEX]
                if open_hfq <= 0:
                    open_hfq = 0.01
                each_quarter_stock_row[stock_info.AMPLITUDE_INDEX] = (each_quarter_stock_row[stock_info.HIGH_INDEX] - each_quarter_stock_row[
                    stock_info.LOW_INDEX]) / each_quarter_stock_row[stock_info.OPEN_INDEX] * 100.0
                each_quarter_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_quarter_stock_row[stock_info.HIGH_HFQ_INDEX] - each_quarter_stock_row[stock_info.LOW_HFQ_INDEX]) / open_hfq * 100.0
                each_quarter_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_quarter_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] - each_quarter_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX]) / each_quarter_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX] * 100.0
                if last_close is None:
                    last_close = each_quarter_stock_row[stock_info.OPEN_INDEX]
                each_quarter_stock_row[stock_info.FLUCTUATE_INDEX] = (each_quarter_stock_row[
                                                                 stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                last_close = each_quarter_stock_row[stock_info.CLOSE_INDEX]
                if last_close_hfq is None:
                    last_close_hfq = each_quarter_stock_row[stock_info.OPEN_HFQ_INDEX]
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                if last_close_hfq <= 0:
                    last_close_hfq = 0.01
                each_quarter_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_quarter_stock_row[
                                                                     stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                last_close_hfq = each_quarter_stock_row[stock_info.CLOSE_HFQ_INDEX]
                if last_close_hfq_by_factor is None:
                    last_close_hfq_by_factor = each_quarter_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                each_quarter_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_quarter_stock_row[
                                                                               stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                last_close_hfq_by_factor = each_quarter_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                quarter_stock_rows.append(each_quarter_stock_row)
                each_quarter_stock_row = None
        if len(quarter_stock_rows) > 0:
            sql = (('INSERT INTO {} (`code`, `date`, `open`, `open_hfq`, `open_hfq_by_factor`, '
                    '`close`, `close_hfq`, `close_hfq_by_factor`, `high`, `high_hfq`, `high_hfq_by_factor`, '
                    '`low`, `low_hfq`, `low_hfq_by_factor`, `amplitude`, `amplitude_hfq`, `amplitude_hfq_by_factor`, '
                    '`fluctuate`, `fluctuate_hfq`, `fluctuate_hfq_by_factor`, `volume`, `amount`, `turnover`) '
                    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,'
                    ' %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    ).format(
                quarter_table_name))
            cursor.executemany(sql, quarter_stock_rows)
            db.commit()
        cursor.close()
        db.close()

    def build_or_update_year_k(self, biz_date):
        code_set = stock_info.get_all_stock_code_set()
        # code_set = ['600900']
        Parallel(n_jobs=multiprocessing.cpu_count())(delayed(StockInfoFetcher.__execute_update_year_k_per_stock)(
            self, code, biz_date) for code in tqdm(code_set, '更新股票交易年线流水表'))

    def __execute_update_year_k_per_stock(self, code, biz_date):
        earliest_date = self.quant_config['stock_info']['earliest_date']
        year_table_name = 'stock_hist_year_k'
        quarter_table_name = 'stock_hist_quarter_k'
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `date`, `close`, `close_hfq`, `close_hfq_by_factor` FROM {} '
                       'WHERE `code` = %s ORDER BY `date` DESC LIMIT 1'.
                       format(year_table_name), code)
        data = cursor.fetchone()
        start_date = (data[0] + relativedelta(years=1)).strftime('%Y%m%d') if data is not None else earliest_date
        last_close = data[1] if data is not None else None
        last_close_hfq = data[2] if data is not None else None
        last_close_hfq_by_factor = data[3] if data is not None else None
        if last_close is None:
            # 发行首周，前日收盘价用首日收盘价/(1+涨跌幅)
            day_table_name = stock_info.get_stock_hist_day_k_table_name(code)
            cursor.execute('SELECT `close`, `fluctuate` FROM {} WHERE `code` = %s ORDER BY date LIMIT 1'.
                           format(day_table_name), code)
            first_data = cursor.fetchone()
            if first_data is None:
                # 还没有交易数据的股票，跳过
                return
            last_close = first_data[0] / (1.0 + first_data[1] / 100.0)
            last_close_hfq = last_close
            last_close_hfq_by_factor = last_close_hfq
        end_date = time_util.nearest_last_last_day_of_year(biz_date).strftime('%Y%m%d')
        quarter_stock_sql = 'SELECT * FROM {} WHERE `code`=%s AND (`date` between %s and %s)'.format(quarter_table_name)
        cursor.execute(quarter_stock_sql, (code, start_date, end_date))
        data = cursor.fetchall()
        if len(data) == 0:
            return
        quarter_stock_rows = list()
        for row in data:
            quarter_stock_row = list(row)
            quarter_stock_row.append(time_util.nearest_last_first_day_of_year(row[stock_info.DATE_INDEX]))
            quarter_stock_rows.append(quarter_stock_row)
        year_stock_rows = list()
        each_year_stock_row = None
        each_year_date = None
        for index, quarter_stock_row in enumerate(quarter_stock_rows):
            if each_year_date != quarter_stock_row[self.GROUP_DATE_INDEX]:
                if each_year_stock_row is not None:
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    open_hfq = each_year_stock_row[stock_info.OPEN_HFQ_INDEX]
                    if open_hfq <= 0:
                        open_hfq = 0.01
                    each_year_stock_row[stock_info.AMPLITUDE_INDEX] = (each_year_stock_row[stock_info.HIGH_INDEX] - each_year_stock_row[stock_info.LOW_INDEX]) / each_year_stock_row[stock_info.OPEN_INDEX] * 100.0
                    each_year_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_year_stock_row[stock_info.HIGH_HFQ_INDEX] - each_year_stock_row[stock_info.LOW_HFQ_INDEX]) / open_hfq * 100.0
                    each_year_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_year_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] - each_year_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX]) / each_year_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX] * 100.0
                    if last_close is None:
                        last_close = each_year_stock_row[stock_info.OPEN_INDEX]
                    each_year_stock_row[stock_info.FLUCTUATE_INDEX] = (each_year_stock_row[stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                    last_close = each_year_stock_row[stock_info.CLOSE_INDEX]
                    if last_close_hfq is None:
                        last_close_hfq = each_year_stock_row[stock_info.OPEN_HFQ_INDEX]
                    # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                    if last_close_hfq <= 0:
                        last_close_hfq = 0.01
                    each_year_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_year_stock_row[
                                                                            stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                    last_close_hfq = each_year_stock_row[stock_info.CLOSE_HFQ_INDEX]
                    if last_close_hfq_by_factor is None:
                        last_close_hfq_by_factor = each_year_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                    each_year_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_year_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                    last_close_hfq_by_factor = each_year_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                    year_stock_rows.append(each_year_stock_row)
                each_year_date = quarter_stock_row[self.GROUP_DATE_INDEX]
                each_year_stock_row = list()
                each_year_stock_row.append(quarter_stock_row[stock_info.CODE_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.GROUP_DATE_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.OPEN_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.OPEN_HFQ_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.CLOSE_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.CLOSE_HFQ_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.HIGH_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.LOW_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.LOW_HFQ_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.AMPLITUDE_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.AMPLITUDE_HFQ_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.FLUCTUATE_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.FLUCTUATE_HFQ_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.VOLUME_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.AMOUNT_INDEX])
                each_year_stock_row.append(quarter_stock_row[stock_info.TURNOVER_INDEX])
            else:
                each_year_stock_row[stock_info.CLOSE_INDEX] = quarter_stock_row[stock_info.CLOSE_INDEX]
                each_year_stock_row[stock_info.CLOSE_HFQ_INDEX] = quarter_stock_row[stock_info.CLOSE_HFQ_INDEX]
                each_year_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] = quarter_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                each_year_stock_row[stock_info.HIGH_INDEX] = max(quarter_stock_row[stock_info.HIGH_INDEX], each_year_stock_row[stock_info.HIGH_INDEX])
                each_year_stock_row[stock_info.HIGH_HFQ_INDEX] = max(quarter_stock_row[stock_info.HIGH_HFQ_INDEX], each_year_stock_row[stock_info.HIGH_HFQ_INDEX])
                each_year_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] = max(
                    quarter_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX],
                    each_year_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row[stock_info.LOW_INDEX] = min(quarter_stock_row[stock_info.LOW_INDEX], each_year_stock_row[stock_info.LOW_INDEX])
                each_year_stock_row[stock_info.LOW_HFQ_INDEX] = min(quarter_stock_row[stock_info.LOW_HFQ_INDEX], each_year_stock_row[stock_info.LOW_HFQ_INDEX])
                each_year_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX] = min(quarter_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX], each_year_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX])
                each_year_stock_row[stock_info.VOLUME_INDEX] += quarter_stock_row[stock_info.VOLUME_INDEX]
                each_year_stock_row[stock_info.AMOUNT_INDEX] += quarter_stock_row[stock_info.AMOUNT_INDEX]
                each_year_stock_row[stock_info.TURNOVER_INDEX] += quarter_stock_row[stock_info.TURNOVER_INDEX]
            if index == len(quarter_stock_rows) - 1:
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                open_hfq = each_year_stock_row[stock_info.OPEN_HFQ_INDEX]
                if open_hfq <= 0:
                    open_hfq = 0.01
                each_year_stock_row[stock_info.AMPLITUDE_INDEX] = (each_year_stock_row[stock_info.HIGH_INDEX] - each_year_stock_row[stock_info.LOW_INDEX]) / each_year_stock_row[stock_info.OPEN_INDEX] * 100.0
                each_year_stock_row[stock_info.AMPLITUDE_HFQ_INDEX] = (each_year_stock_row[stock_info.HIGH_HFQ_INDEX] - each_year_stock_row[stock_info.LOW_HFQ_INDEX]) / open_hfq * 100.0
                each_year_stock_row[stock_info.AMPLITUDE_HFQ_BY_FACTOR_INDEX] = (each_year_stock_row[stock_info.HIGH_HFQ_BY_FACTOR_INDEX] - each_year_stock_row[stock_info.LOW_HFQ_BY_FACTOR_INDEX]) / each_year_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX] * 100.0
                if last_close is None:
                    last_close = each_year_stock_row[stock_info.OPEN_INDEX]
                each_year_stock_row[stock_info.FLUCTUATE_INDEX] = (each_year_stock_row[
                                                                    stock_info.CLOSE_INDEX] - last_close) / last_close * 100.0
                last_close = each_year_stock_row[stock_info.CLOSE_INDEX]
                if last_close_hfq is None:
                    last_close_hfq = each_year_stock_row[stock_info.OPEN_HFQ_INDEX]
                # 加减后复权会出现<=0的情况？不理解先做特殊处理避免出现除0
                if last_close_hfq <= 0:
                    last_close_hfq = 0.01
                each_year_stock_row[stock_info.FLUCTUATE_HFQ_INDEX] = (each_year_stock_row[
                                                                        stock_info.CLOSE_HFQ_INDEX] - last_close_hfq) / last_close_hfq * 100.0
                last_close_hfq = each_year_stock_row[stock_info.CLOSE_HFQ_INDEX]
                if last_close_hfq_by_factor is None:
                    last_close_hfq_by_factor = each_year_stock_row[stock_info.OPEN_HFQ_BY_FACTOR_INDEX]
                each_year_stock_row[stock_info.FLUCTUATE_HFQ_BY_FACTOR_INDEX] = (each_year_stock_row[
                                                                                  stock_info.CLOSE_HFQ_BY_FACTOR_INDEX] - last_close_hfq_by_factor) / last_close_hfq_by_factor * 100.0
                last_close_hfq_by_factor = each_year_stock_row[stock_info.CLOSE_HFQ_BY_FACTOR_INDEX]
                year_stock_rows.append(each_year_stock_row)
                each_year_stock_row = None
        if len(year_stock_rows) > 0:
            sql = (('INSERT INTO {} (`code`, `date`, `open`, `open_hfq`, `open_hfq_by_factor`, '
                    '`close`, `close_hfq`, `close_hfq_by_factor`, `high`, `high_hfq`, `high_hfq_by_factor`, '
                    '`low`, `low_hfq`, `low_hfq_by_factor`, `amplitude`, `amplitude_hfq`, `amplitude_hfq_by_factor`, '
                    '`fluctuate`, `fluctuate_hfq`, `fluctuate_hfq_by_factor`, `volume`, `amount`, `turnover`) '
                    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,'
                    ' %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                    ).format(
                year_table_name))
            cursor.executemany(sql, year_stock_rows)
            db.commit()
        cursor.close()
        db.close()

    def __get_hfq_factors_by_code(self, code):
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('SELECT `date`, `hfq_factor` FROM code_to_hfq_factor'
                       ' WHERE `code` = %s order by `date`', code)
        data = cursor.fetchall()
        hfq_factor_array = list()  # 复权因子数组
        for row in data:
            hfq_factor_array.append((row[0], row[1]))
        cursor.close()
        db.close()
        return hfq_factor_array

    def init_tables(self):
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute(code_to_name_create_sql)
        cursor.execute(code_to_hfq_factor_create_sql)
        for i in range(20):
            cursor.execute(stock_hist_day_k_create_sql.format(i))
        for i in range(5):
            cursor.execute(stock_hist_week_k_create_sql.format(i))
        cursor.execute(stock_hist_month_k_create_sql)
        cursor.execute(stock_hist_quarter_k_create_sql)
        cursor.execute(stock_hist_year_k_create_sql)
        db.commit()
        cursor.close()
        db.close()

    def clear_all_data(self):
        db = stock_info.new_db_conn()
        cursor = db.cursor()
        cursor.execute('DROP TABLE IF EXISTS code_to_name')
        cursor.execute('DROP TABLE IF EXISTS code_to_hfq_factor')
        for i in range(20):
            cursor.execute('DROP TABLE IF EXISTS stock_hist_day_k_{}'.format(str(i)))
            db.commit()
        for i in range(5):
            cursor.execute('DROP TABLE IF EXISTS stock_hist_week_k_{}'.format(str(i)))
            db.commit()
        for i in range(1):
            cursor.execute('DROP TABLE IF EXISTS stock_hist_quarter_k')
            db.commit()
        for i in range(1):
            cursor.execute('DROP TABLE IF EXISTS stock_hist_month_k')
            db.commit()
        for i in range(1):
            cursor.execute('DROP TABLE IF EXISTS stock_hist_year_k')
            db.commit()
        cursor.close()
        db.close()


code_to_name_create_sql = '''
create table if not exists `code_to_name` (
    `id` INT UNSIGNED NOT NULL COMMENT '从0开始的编号',
    `code` VARCHAR(20) NOT NULL COMMENT '股票代码',
    `name` VARCHAR(50) NOT NULL COMMENT '股票简称',
    PRIMARY KEY ( `id` ),
    UNIQUE KEY (`code`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '股票id code name的映射';
'''

code_to_hfq_factor_create_sql = '''
create table if not exists `code_to_hfq_factor` (
    `code` VARCHAR(20) NOT NULL COMMENT '股票代码',
    `hfq_factor` DOUBLE NOT NULL COMMENT '后复权因子',
    `date` TIMESTAMP NOT NULL COMMENT '复权生效时间',
    KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '股票后复权因子查找表';
'''

stock_hist_day_k_create_sql = '''
create table if not exists `stock_hist_day_k_{}` (
    `code` VARCHAR(20) NOT NULL COMMENT '股票代码',
    `date` TIMESTAMP NOT NULL COMMENT '交易日期',
    `open` DOUBLE NOT NULL COMMENT '开盘-不复权',
    `open_hfq` DOUBLE NOT NULL COMMENT '开盘-后复权（加减）',
    `open_hfq_by_factor` DOUBLE NOT NULL COMMENT '开盘-后复权（比例）',
    `close` DOUBLE NOT NULL COMMENT '收盘-不复权',
    `close_hfq` DOUBLE NOT NULL COMMENT '收盘-后复权（加减）',
    `close_hfq_by_factor` DOUBLE NOT NULL COMMENT '收盘-后复权（比例）',
    `high` DOUBLE NOT NULL COMMENT '最高-不复权',
    `high_hfq` DOUBLE NOT NULL COMMENT '最高-后复权（加减）',
    `high_hfq_by_factor` DOUBLE NOT NULL COMMENT '最高-后复权（比例）',
    `low` DOUBLE NOT NULL COMMENT '最低-不复权',
    `low_hfq` DOUBLE NOT NULL COMMENT '最低-后复权（加减）',
    `low_hfq_by_factor` DOUBLE NOT NULL COMMENT '最低-后复权（比例）',
    `amplitude` DOUBLE NOT NULL COMMENT '振幅(单位：%)-不复权',
    `amplitude_hfq` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（加减）',
    `amplitude_hfq_by_factor` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（比例）',
    `fluctuate` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-不复权',
    `fluctuate_hfq` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-后复权（加减）',
    `fluctuate_hfq_by_factor` DOUBLE NOT NULL COMMENT '涨跌幅-后复权（比例）',
    `volume` DOUBLE NOT NULL COMMENT '成交量（单位：手）-和复权方式无关',
    `amount` DOUBLE NOT NULL COMMENT '成交额（单位：元）-和复权方式无关',
    `turnover` DOUBLE NOT NULL COMMENT '换手率(单位：%)=成交量/流动股本-和复权方式无关',
    KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '股票交易日线流水表';
'''

stock_hist_week_k_create_sql = '''
create table if not exists `stock_hist_week_k_{}` (
    `code` VARCHAR(20) NOT NULL COMMENT '股票代码',
    `date` TIMESTAMP NOT NULL COMMENT '交易日期',
    `open` DOUBLE NOT NULL COMMENT '开盘-不复权',
    `open_hfq` DOUBLE NOT NULL COMMENT '开盘-后复权（加减）',
    `open_hfq_by_factor` DOUBLE NOT NULL COMMENT '开盘-后复权（比例）',
    `close` DOUBLE NOT NULL COMMENT '收盘-不复权',
    `close_hfq` DOUBLE NOT NULL COMMENT '收盘-后复权（加减）',
    `close_hfq_by_factor` DOUBLE NOT NULL COMMENT '收盘-后复权（比例）',
    `high` DOUBLE NOT NULL COMMENT '最高-不复权',
    `high_hfq` DOUBLE NOT NULL COMMENT '最高-后复权（加减）',
    `high_hfq_by_factor` DOUBLE NOT NULL COMMENT '最高-后复权（比例）',
    `low` DOUBLE NOT NULL COMMENT '最低-不复权',
    `low_hfq` DOUBLE NOT NULL COMMENT '最低-后复权（加减）',
    `low_hfq_by_factor` DOUBLE NOT NULL COMMENT '最低-后复权（比例）',
    `amplitude` DOUBLE NOT NULL COMMENT '振幅(单位：%)-不复权',
    `amplitude_hfq` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（加减）',
    `amplitude_hfq_by_factor` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（比例）',
    `fluctuate` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-不复权',
    `fluctuate_hfq` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-后复权（加减）',
    `fluctuate_hfq_by_factor` DOUBLE NOT NULL COMMENT '涨跌幅-后复权（比例）',
    `volume` DOUBLE NOT NULL COMMENT '成交量（单位：手）-和复权方式无关',
    `amount` DOUBLE NOT NULL COMMENT '成交额（单位：元）-和复权方式无关',
    `turnover` DOUBLE NOT NULL COMMENT '换手率(单位：%)=成交量/流动股本-和复权方式无关',
    KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '股票交易周线流水表';
'''

stock_hist_month_k_create_sql = '''
create table if not exists `stock_hist_month_k` (
    `code` VARCHAR(20) NOT NULL COMMENT '股票代码',
    `date` TIMESTAMP NOT NULL COMMENT '交易日期',
    `open` DOUBLE NOT NULL COMMENT '开盘-不复权',
    `open_hfq` DOUBLE NOT NULL COMMENT '开盘-后复权（加减）',
    `open_hfq_by_factor` DOUBLE NOT NULL COMMENT '开盘-后复权（比例）',
    `close` DOUBLE NOT NULL COMMENT '收盘-不复权',
    `close_hfq` DOUBLE NOT NULL COMMENT '收盘-后复权（加减）',
    `close_hfq_by_factor` DOUBLE NOT NULL COMMENT '收盘-后复权（比例）',
    `high` DOUBLE NOT NULL COMMENT '最高-不复权',
    `high_hfq` DOUBLE NOT NULL COMMENT '最高-后复权（加减）',
    `high_hfq_by_factor` DOUBLE NOT NULL COMMENT '最高-后复权（比例）',
    `low` DOUBLE NOT NULL COMMENT '最低-不复权',
    `low_hfq` DOUBLE NOT NULL COMMENT '最低-后复权（加减）',
    `low_hfq_by_factor` DOUBLE NOT NULL COMMENT '最低-后复权（比例）',
    `amplitude` DOUBLE NOT NULL COMMENT '振幅(单位：%)-不复权',
    `amplitude_hfq` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（加减）',
    `amplitude_hfq_by_factor` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（比例）',
    `fluctuate` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-不复权',
    `fluctuate_hfq` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-后复权（加减）',
    `fluctuate_hfq_by_factor` DOUBLE NOT NULL COMMENT '涨跌幅-后复权（比例）',
    `volume` DOUBLE NOT NULL COMMENT '成交量（单位：手）-和复权方式无关',
    `amount` DOUBLE NOT NULL COMMENT '成交额（单位：元）-和复权方式无关',
    `turnover` DOUBLE NOT NULL COMMENT '换手率(单位：%)=成交量/流动股本-和复权方式无关',
    KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '股票交易月线流水表';
'''

stock_hist_quarter_k_create_sql = '''
create table if not exists `stock_hist_quarter_k` (
    `code` VARCHAR(20) NOT NULL COMMENT '股票代码',
    `date` TIMESTAMP NOT NULL COMMENT '交易日期',
    `open` DOUBLE NOT NULL COMMENT '开盘-不复权',
    `open_hfq` DOUBLE NOT NULL COMMENT '开盘-后复权（加减）',
    `open_hfq_by_factor` DOUBLE NOT NULL COMMENT '开盘-后复权（比例）',
    `close` DOUBLE NOT NULL COMMENT '收盘-不复权',
    `close_hfq` DOUBLE NOT NULL COMMENT '收盘-后复权（加减）',
    `close_hfq_by_factor` DOUBLE NOT NULL COMMENT '收盘-后复权（比例）',
    `high` DOUBLE NOT NULL COMMENT '最高-不复权',
    `high_hfq` DOUBLE NOT NULL COMMENT '最高-后复权（加减）',
    `high_hfq_by_factor` DOUBLE NOT NULL COMMENT '最高-后复权（比例）',
    `low` DOUBLE NOT NULL COMMENT '最低-不复权',
    `low_hfq` DOUBLE NOT NULL COMMENT '最低-后复权（加减）',
    `low_hfq_by_factor` DOUBLE NOT NULL COMMENT '最低-后复权（比例）',
    `amplitude` DOUBLE NOT NULL COMMENT '振幅(单位：%)-不复权',
    `amplitude_hfq` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（加减）',
    `amplitude_hfq_by_factor` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（比例）',
    `fluctuate` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-不复权',
    `fluctuate_hfq` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-后复权（加减）',
    `fluctuate_hfq_by_factor` DOUBLE NOT NULL COMMENT '涨跌幅-后复权（比例）',
    `volume` DOUBLE NOT NULL COMMENT '成交量（单位：手）-和复权方式无关',
    `amount` DOUBLE NOT NULL COMMENT '成交额（单位：元）-和复权方式无关',
    `turnover` DOUBLE NOT NULL COMMENT '换手率(单位：%)=成交量/流动股本-和复权方式无关',
    KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '股票交易季线流水表';
'''

stock_hist_year_k_create_sql = '''
create table if not exists `stock_hist_year_k` (
    `code` VARCHAR(20) NOT NULL COMMENT '股票代码',
    `date` TIMESTAMP NOT NULL COMMENT '交易日期',
    `open` DOUBLE NOT NULL COMMENT '开盘-不复权',
    `open_hfq` DOUBLE NOT NULL COMMENT '开盘-后复权（加减）',
    `open_hfq_by_factor` DOUBLE NOT NULL COMMENT '开盘-后复权（比例）',
    `close` DOUBLE NOT NULL COMMENT '收盘-不复权',
    `close_hfq` DOUBLE NOT NULL COMMENT '收盘-后复权（加减）',
    `close_hfq_by_factor` DOUBLE NOT NULL COMMENT '收盘-后复权（比例）',
    `high` DOUBLE NOT NULL COMMENT '最高-不复权',
    `high_hfq` DOUBLE NOT NULL COMMENT '最高-后复权（加减）',
    `high_hfq_by_factor` DOUBLE NOT NULL COMMENT '最高-后复权（比例）',
    `low` DOUBLE NOT NULL COMMENT '最低-不复权',
    `low_hfq` DOUBLE NOT NULL COMMENT '最低-后复权（加减）',
    `low_hfq_by_factor` DOUBLE NOT NULL COMMENT '最低-后复权（比例）',
    `amplitude` DOUBLE NOT NULL COMMENT '振幅(单位：%)-不复权',
    `amplitude_hfq` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（加减）',
    `amplitude_hfq_by_factor` DOUBLE NOT NULL COMMENT '振幅(单位：%)-后复权（比例）',
    `fluctuate` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-不复权',
    `fluctuate_hfq` DOUBLE NOT NULL COMMENT '涨跌幅(单位：%)-后复权（加减）',
    `fluctuate_hfq_by_factor` DOUBLE NOT NULL COMMENT '涨跌幅-后复权（比例）',
    `volume` DOUBLE NOT NULL COMMENT '成交量（单位：手）-和复权方式无关',
    `amount` DOUBLE NOT NULL COMMENT '成交额（单位：元）-和复权方式无关',
    `turnover` DOUBLE NOT NULL COMMENT '换手率(单位：%)=成交量/流动股本-和复权方式无关',
    KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '股票交易年线流水表';
'''
